﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;


public class SqlHelper
{
    public static string connstr1 = "server=.;uid=sa;pwd=123456;database=rj1301";
    //数据库的增删改查
    #region
    public static int testDB(string cmd, string connstr, out string errorinfo)
    {
        OleDbConnection conn = new OleDbConnection(connstr);
        try
        {
            conn.Open();
            OleDbCommand mycomd = new OleDbCommand(cmd, conn);
            int i = mycomd.ExecuteNonQuery();
            conn.Close();
            errorinfo = "执行成功!";
        }
        catch (Exception ex)
        {
            errorinfo = ex.Message;
            return 0;
        }

        return 1;


    }
    public static int ExecuteScalar(string cmdText, params SqlParameter[] pms)
    {

        SqlConnection conn = new SqlConnection(connstr1);
        conn.Open();
        SqlCommand cmd = new SqlCommand(cmdText, conn);
        if (pms != null)
        {
            foreach (SqlParameter item in pms)
            {
                if (item != null)
                {
                    cmd.Parameters.Add(item);
                }
            }
        }
        int i = Convert.ToInt32(cmd.ExecuteScalar());
        conn.Close();
        return i;
    }
    public static int ExecuteNonQuery(string cmdText, string connstr, out string ErrorInfo, params SqlParameter[] pms)
    {
        SqlConnection conn = new SqlConnection(connstr);
        try
        {

            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
               
                conn.Close();
                ErrorInfo = "连接字符串错误！请检查登录信息及数据表选择是否正确！";
                return 0;
            }
             
            SqlCommand cmd = new SqlCommand(cmdText, conn);
            if (pms != null)
            {
                foreach (SqlParameter item in pms)
                {
                    if (item != null)
                    {
                        cmd.Parameters.Add(item);
                    }
                }
            }
            int i = Convert.ToInt32(cmd.ExecuteNonQuery());
            conn.Close();
            ErrorInfo = "执行成功！";
        }
        catch (Exception e)
        {
            ErrorInfo = e.Message;
            conn.Close();
            return 0;
        }
       
        return 1;

    }
   
    #endregion
    //返回一张数据表
    public static DataTable GetTable(string cmdText, string connstr, out string error, params SqlParameter[] pms)
    {
        DataTable dt = new DataTable();
        try
        {
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(cmdText, conn);
            if (pms != null)
            {
                foreach (SqlParameter item in pms)
                {
                    if (item != null)
                    {
                        cmd.Parameters.Add(item);
                    }
                }
            }
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            sda.Fill(ds, "aa");
            conn.Close();
            dt = ds.Tables["aa"];
            error="";
            return dt;
        }
        catch (Exception ex)
        {
            error=ex.Message;
            return null;
        }
      
       
    }
    //返回查询数据集合
    public static SqlDataReader GetReader(string cmdText, string connstr, params SqlParameter[] pms)
    {
        SqlConnection conn = new SqlConnection(connstr);
        conn.Open();
        SqlCommand cmd = new SqlCommand(cmdText, conn);
        if (pms != null)
        {
            foreach (SqlParameter item in pms)
            {
                if (item != null)
                {
                    cmd.Parameters.Add(item);
                }
            }
        }
        SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//把数据取出来以游标的形式放入sdr
        return sdr;
    }
    //测试是否连接成功
    #region
    public static string constrSocurce = "";
    public static string constrDest = "";
    public static bool Connection(string[] constr, out string stateinfo)
    {
        string connstr = "server=";
        connstr += constr[0] + ";uid=";
        connstr += constr[1] + ";pwd=";
        connstr += constr[2] + ";database=";
        constrSocurce = connstr + constr[3];
        constrDest = connstr + constr[4];
        SqlConnection connSoc = new SqlConnection(constrSocurce);
        SqlConnection connDest = new SqlConnection(constrDest);
        string info = "";
        try
        {
            connSoc.Open();
            connSoc.Close();

        }
        catch (Exception e)
        {

            info = e.Message + "请检查你的登录信息是否输入正确。";
            constrSocurce = "";

        }
        try
        {
            connDest.Open();
            connDest.Close();
        }
        catch (Exception e)
        {

            info += "\n" + e.Message + "请检查你的登录信息是否输入正确。";
            constrDest = "";
        }
        stateinfo = info;
        if (stateinfo == "")
        {
            return true;
        }
        else
        {
            return false;
        }

    }
    #endregion
}

